package vip.ipav.poi.excel.writer;

/**
 * Created by doobo@foxmail.com on 2017/11/21.
 */
import java.io.*;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel超大数据写入，抽象excel2007读入器，先构建.xlsx一张模板，改写模板中的sheet.xml,
 * 使用这种方法 写入.xlsx文件，不需要太大的内存
 * 注意：其生产的表名为sheet1,但rId为3，用rId读取时，指定为3才能读取到数据
 * @version 2017-11-21
 */
public class Excel2007Writer {

    private SpreadsheetWriter sw;
    private OutputStream out;
    private List<List<Object>> list;

    public Excel2007Writer(String path) throws FileNotFoundException {
        this.out = new FileOutputStream(path);
    }

    public Excel2007Writer(OutputStream out){
        this.out = out;
    }

    /**
     * 写入电子表格的主要流程
     *
     * @param list
     * @throws Exception
     */
    public void process(List<List<Object>> list) throws Exception {
        //写入Excel的数据
        this.list = list;
        // 建立工作簿和电子表格对象
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");

        // 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
        String sheetRef = sheet.getPackagePart().getPartName().getName();

        // 保存模板
        FileOutputStream os = new FileOutputStream("template.xlsx");
        wb.write(os);
        os.close();

        // 生成xml文件
        File tmp = File.createTempFile("sheet", ".xml");
        Writer fw = new FileWriter(tmp);
        sw = new SpreadsheetWriter(fw);
        generate();
        fw.close();

        // 使用产生的数据替换模板
        File templateFile = new File("template.xlsx");
        substitute(templateFile, tmp, sheetRef.substring(1), out);
        if(out != null){out.close();}

        // 删除文件之前调用一下垃圾回收器，否则无法删除模板文件
        System.gc();
        // 删除临时模板文件
        if (templateFile.isFile() && templateFile.exists()) {
            templateFile.delete();
        }
    }

    /**
     * 类使用者应该使用此方法进行写操作
     *
     * @throws Exception
     */
    public void generate() throws Exception{
        Iterator<List<Object>> it = this.list.iterator();
        int i = 0;
        this.beginSheet();
        while (it.hasNext()){
            this.insertRow(i++);
            int j = 0;
            Iterator itt = it.next().iterator();
            while (itt.hasNext()){
                Object obj = itt.next();
                if(obj instanceof String){
                    this.createCell(j++,(String) obj);
                }else if(obj instanceof Integer){
                    this.createCell(j++,Double.parseDouble(obj.toString()));
                }else if(obj instanceof Double){
                    this.createCell(j++,(Double) obj);
                }else if(obj instanceof Boolean){
                    this.createCell(j++,(Boolean) obj);
                }else {
                    this.createCell(j++,obj.toString());
                }
            }
            this.endRow();
        }
        this.endSheet();
    };

    public void beginSheet() throws IOException {
        sw.beginSheet();
    }

    public void insertRow(int rowNum) throws IOException {
        sw.insertRow(rowNum);
    }

    public void createCell(int columnIndex, String value) throws IOException {
        sw.createCell(columnIndex, value, -1);
    }

    public void createCell(int columnIndex, double value) throws IOException {
        sw.createCell(columnIndex, value, -1);
    }

    public void createCell(int columnIndex, Boolean value) throws IOException {
        sw.createCell(columnIndex, value);
    }
    public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
        sw.createCell(columnIndex, value, styleIndex);
    }

    public void endRow() throws IOException {
        sw.endRow();
    }

    public void endSheet() throws IOException {
        sw.endSheet();
    }

    /**
     *
     * @param zipfile
     *            the template file
     * @param tmpfile
     *            the XML file with the sheet data
     * @param entry
     *            the name of the sheet entry to substitute, e.g.
     *            xl/worksheets/sheet1.xml
     * @param out
     *            the stream to write the result to
     */
    private static void substitute(File zipfile, File tmpfile, String entry,
                                   OutputStream out) throws IOException {
        ZipFile zip = new ZipFile(zipfile);
        ZipOutputStream zos = new ZipOutputStream(out);

        @SuppressWarnings("unchecked")
        Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
        while (en.hasMoreElements()) {
            ZipEntry ze = en.nextElement();
            if (!ze.getName().equals(entry)) {
                zos.putNextEntry(new ZipEntry(ze.getName()));
                InputStream is = zip.getInputStream(ze);
                copyStream(is, zos);
                is.close();
            }
        }
        zos.putNextEntry(new ZipEntry(entry));
        InputStream is = new FileInputStream(tmpfile);
        copyStream(is, zos);
        is.close();
        zos.close();
    }

    private static void copyStream(InputStream in, OutputStream out)
            throws IOException {
        byte[] chunk = new byte[1024];
        int count;
        while ((count = in.read(chunk)) >= 0) {
            out.write(chunk, 0, count);
        }
    }

    /**
     * 在写入器中写入电子表格
     *
     */
    public static class SpreadsheetWriter {
        private final Writer _out;
        private int _rownum;
        private static String LINE_SEPARATOR = System
                .getProperty("line.separator");

        public SpreadsheetWriter(Writer out) {
            _out = out;
        }

        public void beginSheet() throws IOException {
            _out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>"
                    + "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
            _out.write("<sheetData>" + LINE_SEPARATOR);
        }

        public void endSheet() throws IOException {
            _out.write("</sheetData>");
            _out.write("</worksheet>");
        }

        /**
         * 插入新行
         *
         * @param rownum
         *            以0开始
         */
        public void insertRow(int rownum) throws IOException {
            _out.write("<row r=\"" + (rownum + 1) + "\">" + LINE_SEPARATOR);
            this._rownum = rownum;
        }

        /**
         * 插入行结束标志
         */
        public void endRow() throws IOException {
            _out.write("</row>" + LINE_SEPARATOR);
        }

        /**
         * 插入新列
         *
         * @param columnIndex
         * @param value
         * @param styleIndex
         * @throws IOException
         */
        public void createCell(int columnIndex, String value, int styleIndex)
                throws IOException {
            String ref = new CellReference(_rownum, columnIndex)
                    .formatAsString();
            _out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
            if (styleIndex != -1)
                _out.write(" s=\"" + styleIndex + "\"");
            _out.write(">");
            _out.write("<is><t>" + encoderXML(value) + "</t></is>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, String value)
                throws IOException {
            createCell(columnIndex, value, -1);
        }

        /**
         *
         * @param columnIndex
         * @param value
         * @param styleIndex -1 默认整数和小数,2日期浮点型，3百分数
         * @throws IOException
         */
        public void createCell(int columnIndex, double value, int styleIndex)
                throws IOException {
            String ref = new CellReference(_rownum, columnIndex)
                    .formatAsString();
            _out.write("<c r=\"" + ref + "\" t=\"n\"");
            if (styleIndex != -1)
                _out.write(" s=\"" + styleIndex + "\"");
            _out.write(">");
            _out.write("<v>" + value + "</v>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, Boolean value)
                throws IOException {
            String ref = new CellReference(_rownum, columnIndex)
                    .formatAsString();
            _out.write("<c r=\"" + ref + "\" t=\"b\"");
            _out.write(">");
            _out.write("<v>" + (value?1:0) + "</v>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, double value)
                throws IOException {
            createCell(columnIndex, value, -1);
        }


        public void createCell(int columnIndex, Calendar value, int styleIndex)
                throws IOException {
            createCell(columnIndex, DateUtil.getExcelDate(value, false),
                    styleIndex);
        }
    }

    // XML Encode
    private static final String[] xmlCode = new String[256];

    static {
        // Special characters
        xmlCode['\''] = "'";
        xmlCode['\"'] = "\""; // double quote
        xmlCode['&'] = "&"; // ampersand
        xmlCode['<'] = "<"; // lower than
        xmlCode['>'] = ">"; // greater than
    }

    /**
     * <p>
     * Encode the given text into xml.
     * </p>
     *
     * @param string
     *            the text to encode
     * @return the encoded string
     */
    public static String encoderXML(String string) {
        if (string == null)
            return "";
        int n = string.length();
        char character;
        String xmlchar;
        StringBuffer buffer = new StringBuffer();
        // loop over all the characters of the String.
        for (int i = 0; i < n; i++) {
            character = string.charAt(i);
            // the xmlcode of these characters are added to a StringBuffer
            // one by one
            try {
                xmlchar = xmlCode[character];
                if (xmlchar == null) {
                    buffer.append(character);
                } else {
                    buffer.append(xmlCode[character]);
                }
            } catch (ArrayIndexOutOfBoundsException aioobe) {
                buffer.append(character);
            }
        }
        return buffer.toString();
    }
}
